using Oracle.ManagedDataAccess.Client;
using System.Data;
using Lzfy_His_Service.Repositories;

namespace Lzfy_His_Service.Services
{
    /// <summary>
    /// 基于连接工厂的Oracle连接管理器
    /// 遵循Oracle连接最佳实践，使用IOracleConnectionFactory
    /// </summary>
    public class FactoryBasedOracleConnectionManager : IOracleConnectionManager
    {
        private readonly IOracleConnectionFactory _connectionFactory;
        private readonly ILogger<FactoryBasedOracleConnectionManager> _logger;

        public FactoryBasedOracleConnectionManager(
            IOracleConnectionFactory connectionFactory,
            ILogger<FactoryBasedOracleConnectionManager> logger)
        {
            _connectionFactory = connectionFactory;
            _logger = logger;
        }

        #region 基础查询方法 - 使用连接工厂

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        public async Task<int> ExecuteSqlAsync(string sql, params OracleParameter[] parameters)
        {
            var stopwatch = System.Diagnostics.Stopwatch.StartNew();
            var connectionId = Guid.NewGuid();
            
            try
            {
                using var connection = (OracleConnection)_connectionFactory.CreateConnection();
                _logger.LogDebug("[{ConnectionId}] 创建Oracle连接执行SQL: {Sql}", connectionId, sql);
                
                using var cmd = new OracleCommand(sql, connection);
                
                await connection.OpenAsync();
                _logger.LogDebug("[{ConnectionId}] Oracle连接打开成功，耗时: {ElapsedMs}ms", connectionId, stopwatch.ElapsedMilliseconds);
                
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                
                var result = await cmd.ExecuteNonQueryAsync();
                stopwatch.Stop();
                
                _logger.LogInformation("[{ConnectionId}] SQL执行成功，影响行数: {RowCount}，总耗时: {ElapsedMs}ms", connectionId, result, stopwatch.ElapsedMilliseconds);
                return result;
            }
            catch (OracleException ex)
            {
                stopwatch.Stop();
                _logger.LogError(ex, "[{ConnectionId}] Oracle执行SQL失败，耗时: {ElapsedMs}ms，SQL: {Sql}，错误码: {ErrorCode}", 
                    connectionId, stopwatch.ElapsedMilliseconds, sql, ex.Number);
                throw new Exception($"执行SQL失败: {ex.Message}", ex);
            }
            catch (TimeoutException ex)
            {
                stopwatch.Stop();
                _logger.LogError(ex, "[{ConnectionId}] Oracle执行SQL超时，耗时: {ElapsedMs}ms，SQL: {Sql}", 
                    connectionId, stopwatch.ElapsedMilliseconds, sql);
                throw new Exception($"执行SQL超时: {ex.Message}", ex);
            }
            catch (Exception ex)
            {
                stopwatch.Stop();
                _logger.LogError(ex, "[{ConnectionId}] Oracle执行SQL异常，耗时: {ElapsedMs}ms，SQL: {Sql}", 
                    connectionId, stopwatch.ElapsedMilliseconds, sql);
                throw;
            }
        }

        /// <summary>
        /// 执行查询语句，返回DataTable
        /// </summary>
        public async Task<DataTable> GetDataTableAsync(string sql, params OracleParameter[] parameters)
        {
            var stopwatch = System.Diagnostics.Stopwatch.StartNew();
            var connectionId = Guid.NewGuid();
            
            try
            {
                using var connection = (OracleConnection)_connectionFactory.CreateConnection();
                _logger.LogDebug("[{ConnectionId}] 创建Oracle连接查询DataTable: {Sql}", connectionId, sql);
                
                using var cmd = new OracleCommand(sql, connection);
                
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                
                using var adapter = new OracleDataAdapter(cmd);
                await connection.OpenAsync();
                _logger.LogDebug("[{ConnectionId}] Oracle连接打开成功，耗时: {ElapsedMs}ms", connectionId, stopwatch.ElapsedMilliseconds);
                
                var dataTable = new DataTable();
                adapter.Fill(dataTable);
                stopwatch.Stop();
                
                _logger.LogInformation("[{ConnectionId}] DataTable查询成功，返回行数: {RowCount}，总耗时: {ElapsedMs}ms", 
                    connectionId, dataTable.Rows.Count, stopwatch.ElapsedMilliseconds);
                return dataTable;
            }
            catch (OracleException ex)
            {
                stopwatch.Stop();
                _logger.LogError(ex, "[{ConnectionId}] Oracle查询DataTable失败，耗时: {ElapsedMs}ms，SQL: {Sql}，错误码: {ErrorCode}", 
                    connectionId, stopwatch.ElapsedMilliseconds, sql, ex.Number);
                throw new Exception($"查询数据失败: {ex.Message}", ex);
            }
            catch (TimeoutException ex)
            {
                stopwatch.Stop();
                _logger.LogError(ex, "[{ConnectionId}] Oracle查询DataTable超时，耗时: {ElapsedMs}ms，SQL: {Sql}", 
                    connectionId, stopwatch.ElapsedMilliseconds, sql);
                throw new Exception($"查询数据超时: {ex.Message}", ex);
            }
            catch (Exception ex)
            {
                stopwatch.Stop();
                _logger.LogError(ex, "[{ConnectionId}] Oracle查询DataTable异常，耗时: {ElapsedMs}ms，SQL: {Sql}", 
                    connectionId, stopwatch.ElapsedMilliseconds, sql);
                throw;
            }
        }

        /// <summary>
        /// 执行查询语句，返回单个值
        /// </summary>
        public async Task<object?> GetSingleAsync(string sql, params OracleParameter[] parameters)
        {
            var stopwatch = System.Diagnostics.Stopwatch.StartNew();
            var connectionId = Guid.NewGuid();
            
            try
            {
                using var connection = (OracleConnection)_connectionFactory.CreateConnection();
                _logger.LogDebug("[{ConnectionId}] 创建Oracle连接查询单个值: {Sql}", connectionId, sql);
                
                using var cmd = new OracleCommand(sql, connection);
                
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                }

                await connection.OpenAsync();
                _logger.LogDebug("[{ConnectionId}] Oracle连接打开成功，耗时: {ElapsedMs}ms", connectionId, stopwatch.ElapsedMilliseconds);
                
                var result = await cmd.ExecuteScalarAsync();
                stopwatch.Stop();
                
                _logger.LogInformation("[{ConnectionId}] 单个值查询成功，返回值: {HasValue}，总耗时: {ElapsedMs}ms", 
                    connectionId, result != null && result != DBNull.Value, stopwatch.ElapsedMilliseconds);
                
                return (result == null || result == DBNull.Value) ? null : result;
            }
            catch (OracleException ex)
            {
                stopwatch.Stop();
                _logger.LogError(ex, "[{ConnectionId}] Oracle查询单个值失败，耗时: {ElapsedMs}ms，SQL: {Sql}，错误码: {ErrorCode}", 
                    connectionId, stopwatch.ElapsedMilliseconds, sql, ex.Number);
                throw new Exception($"获取单个值失败: {ex.Message}", ex);
            }
            catch (TimeoutException ex)
            {
                stopwatch.Stop();
                _logger.LogError(ex, "[{ConnectionId}] Oracle查询单个值超时，耗时: {ElapsedMs}ms，SQL: {Sql}", 
                    connectionId, stopwatch.ElapsedMilliseconds, sql);
                throw new Exception($"获取单个值超时: {ex.Message}", ex);
            }
            catch (Exception ex)
            {
                stopwatch.Stop();
                _logger.LogError(ex, "[{ConnectionId}] Oracle查询单个值异常，耗时: {ElapsedMs}ms，SQL: {Sql}", 
                    connectionId, stopwatch.ElapsedMilliseconds, sql);
                throw;
            }
        }

        /// <summary>
        /// 执行事务操作
        /// </summary>
        public async Task ExecuteSqlTransactionAsync(List<string> sqlList, List<OracleParameter[]>? parameterList = null)
        {
            var stopwatch = System.Diagnostics.Stopwatch.StartNew();
            var connectionId = Guid.NewGuid();
            
            if (sqlList == null || sqlList.Count == 0)
            {
                throw new ArgumentException("SQL列表不能为空", nameof(sqlList));
            }

            using var connection = (OracleConnection)_connectionFactory.CreateConnection();
            _logger.LogDebug("[{ConnectionId}] 创建Oracle连接执行事务，包含SQL数量: {SqlCount}", connectionId, sqlList.Count);
            
            try
            {
                await connection.OpenAsync();
                using var transaction = connection.BeginTransaction();
                using var cmd = new OracleCommand { Connection = connection, Transaction = transaction };
                
                for (int i = 0; i < sqlList.Count; i++)
                {
                    cmd.CommandText = sqlList[i];
                    cmd.Parameters.Clear();

                    if (parameterList != null && parameterList.Count > i && parameterList[i] != null)
                    {
                        cmd.Parameters.AddRange(parameterList[i]);
                    }

                    await cmd.ExecuteNonQueryAsync();
                }

                transaction.Commit();
                stopwatch.Stop();
                
                _logger.LogInformation("[{ConnectionId}] 事务执行成功，共执行 {Count} 条SQL，总耗时: {ElapsedMs}ms", 
                    connectionId, sqlList.Count, stopwatch.ElapsedMilliseconds);
            }
            catch (OracleException ex)
            {
                stopwatch.Stop();
                _logger.LogError(ex, "[{ConnectionId}] Oracle事务执行失败，耗时: {ElapsedMs}ms，错误码: {ErrorCode}", 
                    connectionId, stopwatch.ElapsedMilliseconds, ex.Number);
                throw new Exception($"事务执行失败: {ex.Message}", ex);
            }
            catch (TimeoutException ex)
            {
                stopwatch.Stop();
                _logger.LogError(ex, "[{ConnectionId}] Oracle事务执行超时，耗时: {ElapsedMs}ms", 
                    connectionId, stopwatch.ElapsedMilliseconds);
                throw new Exception($"事务执行超时: {ex.Message}", ex);
            }
            catch (Exception ex)
            {
                stopwatch.Stop();
                _logger.LogError(ex, "[{ConnectionId}] Oracle事务执行异常，耗时: {ElapsedMs}ms", 
                    connectionId, stopwatch.ElapsedMilliseconds);
                throw;
            }
        }

        #endregion

        #region 高级查询方法

        /// <summary>
        /// 执行查询并返回OracleDataReader
        /// </summary>
        public async Task<OracleDataReader> ExecuteReaderAsync(string sql, params OracleParameter[] parameters)
        {
            var stopwatch = System.Diagnostics.Stopwatch.StartNew();
            var connectionId = Guid.NewGuid();
            
            try
            {
                var connection = (OracleConnection)_connectionFactory.CreateConnection();
                _logger.LogDebug("[{ConnectionId}] 创建Oracle连接执行Reader查询: {Sql}", connectionId, sql);
                
                var cmd = new OracleCommand(sql, connection);
                
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                }

                await connection.OpenAsync();
                stopwatch.Stop();
                
                _logger.LogInformation("[{ConnectionId}] OracleDataReader查询成功，总耗时: {ElapsedMs}ms", 
                    connectionId, stopwatch.ElapsedMilliseconds);
                
                return await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);
            }
            catch (OracleException ex)
            {
                stopwatch.Stop();
                _logger.LogError(ex, "[{ConnectionId}] OracleDataReader查询失败，耗时: {ElapsedMs}ms，SQL: {Sql}，错误码: {ErrorCode}", 
                    connectionId, stopwatch.ElapsedMilliseconds, sql, ex.Number);
                throw new Exception($"执行查询失败: {ex.Message}", ex);
            }
            catch (TimeoutException ex)
            {
                stopwatch.Stop();
                _logger.LogError(ex, "[{ConnectionId}] OracleDataReader查询超时，耗时: {ElapsedMs}ms，SQL: {Sql}", 
                    connectionId, stopwatch.ElapsedMilliseconds, sql);
                throw new Exception($"执行查询超时: {ex.Message}", ex);
            }
            catch (Exception ex)
            {
                stopwatch.Stop();
                _logger.LogError(ex, "[{ConnectionId}] OracleDataReader查询异常，耗时: {ElapsedMs}ms，SQL: {Sql}", 
                    connectionId, stopwatch.ElapsedMilliseconds, sql);
                throw;
            }
        }

        #endregion

        /// <summary>
        /// 获取连接池状态信息
        /// </summary>
        public Dictionary<string, object> GetConnectionPoolStats()
        {
            try
            {
                var stats = new Dictionary<string, object>
                {
                    ["ConnectionFactoryType"] = _connectionFactory.GetType().Name,
                    ["CurrentTime"] = DateTime.Now,
                    ["ConnectionTimeout"] = 60,
                    ["MaxPoolSize"] = 100,
                    ["MinPoolSize"] = 10,
                    ["PoolingEnabled"] = true,
                    ["ValidateConnection"] = true
                };
                
                _logger.LogDebug("获取连接池统计信息: {Stats}", 
                    string.Join(", ", stats.Select(kv => $"{kv.Key}={kv.Value}")));
                
                return stats;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取连接池统计信息失败");
                throw;
            }
        }
    }
}